WITH w_SITE_URL AS
(SELECT t.*, row_number() over(order by t_site.code, t.ORDER_NUM, t.CODE) rn
, t_site.code as siteCode
, t_site.name as siteName,
project_DICTIONARY.code as projectCode ,
project_DICTIONARY.name as projectName
FROM C1_SITE_URL T
left join C1_SITE t_site on (t_site.id = t.site_id)
left join C1_DICTIONARY project_DICTIONARY on (project_DICTIONARY.id = t_site.project_ID)
where 1 = 1
<#if (siteCode??)>
and (instr(','||t_site.code||',', ','||:siteCode||',') > 0 or instr(','||t_site.name||',', ','||:siteCode||',') > 0)
</#if>
<#if (code??)>
and (instr(','||T.code||',', ','||:code||',') > 0 or instr(','||T.name||',', ','||:code||',') > 0)
</#if>
)
SELECT T.BEGIN_DATE AS beginDate
, T.CODE AS code
, T.END_DATE AS endDate
, T.ID AS id
, T.NAME AS name
, T.ORDER_NUM AS orderNum
, T.SITE_ID AS siteId
, T.siteCode
, T.siteName
, t.projectCode
, t.projectName
FROM w_SITE_URL t WHERE t.rn BETWEEN :begin_row_num AND :end_row_num